insert overwrite table jms_dm.dm_customer_fran_structure_dt partition (dt)
select timetype                                                    --时间类型,区分日,周,月
     , date_time                                                   --日期
     , agent_code                                                  --代理区code
     , agent_name                                                  --代理区
     , total_fran_num                                              --代理区下总加盟商数
     , head_fran_num                                               --头部加盟商个数
     , round(head_fran_num / total_fran_num, 4) as head_fran_ratio --头部加盟商占比
     , tail_fran_num                                               --尾部加盟商个数
     , round(tail_fran_num / total_fran_num, 4) as tail_fran_ratio --尾部加盟商占比
     , dorisdt
     , dorisdt                                  as dt
from (
         select timetype
              , date_time                                                      --日期
              , dorisdt
              , agent_name                                                     --代理区
              , agent_code                                                     --代理区code
              , total_fran_num                                                 --代理区下总加盟商数
              , min(if(percent_desc = 1, row_num_desc, 9999)) as head_fran_num --头部加盟商个数
              , min(if(percent_asc = 1, row_num_asc, 9999))   as tail_fran_num --尾部加盟商个数
         from (
                  select timetype
                       , date_time        --日期
                       , dorisdt
                       , agent_name       --代理区
                       , agent_code       --代理区code
                       , if((total_ticket_quantity_desc / total_ticket_num) >= 0.5, 1, 0) as percent_desc
                       , if((total_ticket_quantity_asc / total_ticket_num) >= 0.2, 1, 0)  as percent_asc
                       , row_num_desc
                       , row_num_asc
                       , total_fran_num   --代理区下总加盟商数
                       , total_ticket_num --代理区下总发件数
                  from (
                           select timetype
                                , date_time                                                                                           --日期
                                , dorisdt
                                , agent_name                                                                                          --代理区
                                , agent_code                                                                                          --代理区code
                                , fran_code                                                                                           --加盟商
                                , waybill_cnt                                                                                         --加盟商对应票数
                                , sum(waybill_cnt)
                                      over (partition by timetype,date_time,agent_code order by waybill_cnt desc) as total_ticket_quantity_desc
                                , sum(waybill_cnt)
                                      over (partition by timetype,date_time,agent_code order by waybill_cnt asc)  as total_ticket_quantity_asc
                                , row_number()
                                   over (partition by timetype,date_time,agent_code order by waybill_cnt desc)    as row_num_desc
                                , row_number()
                                   over (partition by timetype,date_time,agent_code order by waybill_cnt asc)     as row_num_asc
                                , count(fran_code) over (partition by timetype,date_time,agent_code )             as total_fran_num   --代理区下总加盟商数
                                , sum(waybill_cnt) over (partition by timetype,date_time,agent_code )             as total_ticket_num --代理区下总发件数
                           from (
                                    select timetype
                                         , date_time                           --日期
                                         , dorisdt
                                         , agent_name                          --代理区
                                         , agent_code                          --代理区code
                                         , fran_code                           --加盟商
                                         , sum(ticket_quantity) as waybill_cnt --加盟商对应票数
                                    from jms_dm.dm_customer_date_network_level_ticket_detail_dt
                                    where networktype = 2
                                    group by timetype
                                           , date_time  --日期
                                           , dorisdt
                                           , agent_name --代理区
                                           , agent_code --代理区code
                                           , fran_code  --加盟商
                                           , fran_name
                                ) t1
                       ) t2
              ) t3
         group by timetype
                , date_time  --日期
                , dorisdt
                , agent_name --代理区
                , agent_code --代理区code
                , total_fran_num --代理区下总加盟商数
     ) t4

 distribute by dt
;
